# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from scrapy.pipelines.images import ImagesPipeline
from scrapy.exporters import JsonItemExporter
import codecs
import json
import MySQLdb
from twisted.enterprise import adbapi


class ArticlespiderPipeline:
    def process_item(self, item, spider):
        print(item)
        return item


class ArticleImageHandlePipeline(ImagesPipeline):
    """
    自定义处理图片下载逻辑
    """

    # 重载 item_completed
    def item_completed(self, results, item, info):

        if "front_image_url" in item:
            image_file_path = ""

            for ok, value in results:
                # 获取文件下载的路径
                image_file_path = value["path"]

            item["front_image_path"] = image_file_path

        return item


class JsonWithEncodingPipeline(object):
    """
    保存item信息到本地文件
    """

    def __init__(self):
        """
        构造器
        """
        # 打开一个文件
        self.file = codecs.open("article.json", "a", encoding="utf-8")

    # 重写 process_item
    def process_item(self, item, spider):
        # 将item转为 json格式文本
        lines = json.dumps(dict(item), ensure_ascii=False) + "\n"
        # 写入file中
        self.file.write(lines)
        # return item供其他pipeline使用
        return item

    # 重写 spider_closed
    def spider_closed(self):
        # 关闭文件流
        self.file.close()


class JsonExporterPipeline(object):
    """
    scrapy提供的json文件导出
    """

    def __init__(self):
        # 以二进制打开文件
        self.file = open('articleexporter.json', 'wb')
        self.exporter = JsonItemExporter(self.file, encoding="utf-8", ensure_ascii=False)
        self.exporter.start_exporting()

    def process_item(self, item, spider):
        self.exporter.export_item(item)
        return item

    def spider_closed(self, spider):
        self.exporter.finish_exporting()
        self.file.close()


class MysqlSavePipeline(object):
    """
    保存数据库
    """

    def process_item(self, item, spider):
        """
        处理item值
        :param item:
        :param spider:
        :return:
        """
        query = self.dbpool.runInteraction
        insert_sql = """
           INSERT INTO cnblog_article(title,
               url,
               url_object_id,
               front_image_url,
               front_image_path,
               parise_nums,
               comment_nums,
               fav_nums,
               tag,
               content,
               create_date) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
               ON DUPLICATE KEY UPDATE parise_nums = VALUES (parise_nums)
        """
        # 参数
        params = list()
        params.append(item.get("title", ""))
        params.append(item.get("url", ""))
        params.append(item.get("url_object_id", ""))

        front_image_url = ",".join(item.get("front_image_url", []))
        params.append(front_image_url)

        params.append(item.get("front_image_path", ""))
        params.append(item.get("parise_nums", 0))
        params.append(item.get("comment_nums", 0))
        params.append(item.get("fav_nums", 0))
        params.append(item.get("tag", ""))
        params.append(item.get("content", ""))
        params.append(item.get("create_date", "1970-07-01"))
        # 执行sql脚本
        self.cursor.execute(insert_sql, tuple(params))
        # 提交sql事务
        self.conn.commit()
        return item


class MysqlTwistedPipeline(object):
    """
    Twisted 异步数据保存
    """

    def __init__(self, dbpool):
        # 接受cls 返回的db连接池
        self.dbpool = dbpool

    @classmethod
    def from_settings(cls, settings):
        from MySQLdb.cursors import DictCursor
        dbparams = dict(
            host=settings["MYSQL_HOST"],
            db=settings["MYSQL_DBNAME"],
            user=settings["MYSQL_USER"],
            password=settings["MYSQL_PASSWORD"],
            charset="utf8",
            cursorclass=DictCursor,
            use_unicode=True
        )
        # db连接池
        dbpool = adbapi.ConnectionPool("MySQLdb", **dbparams)
        # 返回 连接池
        return cls(dbpool)

    def process_item(self, item, spider):
        query = self.dbpool.runInteraction(self.do_insert, item)
        query.addErrback(self.handle_error, item, spider)

    def handle_error(self, failure, item, spider):
        """
        处理异常回调
        :param failure: 异常信息
        :return:
        """
        print(failure)

    def do_insert(self, cursor, item):
        """
        异步处理方法
        :param item:
        :return:
        """
        insert_sql = """
                  INSERT INTO cnblog_article(title,
                      url,
                      url_object_id,
                      front_image_url,
                      front_image_path,
                      parise_nums,
                      comment_nums,
                      fav_nums,
                      tag,
                      content,
                      create_date) 
                      VALUES
                      (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                      ON DUPLICATE KEY UPDATE parise_nums = VALUES (parise_nums)
               """
        # MYSQL 设置插入时主键重复时进行对应字段的更新操作： ON DUPLICATE KEY UPDATE parise_nums = VALUES (parise_nums)
        # 参数
        params = list()
        params.append(item.get("title", ""))
        params.append(item.get("url", ""))
        params.append(item.get("url_object_id", ""))

        front_image_url = ",".join(item.get("front_image_url", []))
        params.append(front_image_url)

        params.append(item.get("front_image_path", ""))
        params.append(item.get("parise_num", 0))
        params.append(item.get("comment_num", 0))
        params.append(item.get("fav_num", 0))
        params.append(item.get("tags", ""))
        params.append(item.get("content", ""))
        params.append(item.get("create_date", "1970-07-01"))
        # 执行sql脚本
        # tuple(params) 将list 强转为tuple类型
        cursor.execute(insert_sql, tuple(params))
